<?php


namespace App\Command\Model;


use EchoPHP\Facades\Db;
use EchoPHP\Facades\Log;
use EchoPHP\Facades\Redis;
use RuntimeException;

class KpiReport {

    protected $syncDate;
    protected $redisClient;
    //kpi标签与报表字段名对应关系
    protected $kpiCodeInField = [
        '0102' => 'code1',
        '0201' => 'code2',
        '0302' => 'code3',
        '0501' => 'code4',
        '0803' => 'code5',
        '1201' => 'code6',
        '1502' => 'code7',
        '1602' => 'code8',
    ];
    protected $kpiCode = [];

    public function __construct($syncDate) {
        $this->syncDate = $syncDate;
        $this->redisClient = Redis::connect('reportdata');
        $this->redisClient->setOption(\Redis::OPT_SCAN, \Redis::SCAN_RETRY);

        $this->kpiCode = array_keys($this->kpiCodeInField);
    }

    /**
     * 同步数据
     */
    public function sync() {
        //同步顾问个人数据
        $this->syncReportData('operator');
        //同步顾问-客户维度数据
        $this->syncReportData('customer');
        //同步顾问-项目维度数据
        $this->syncReportData('project');
        //同步顾问-职位维度数据
        $this->syncReportData('case');
    }

    /**
     * 报表数据处理逻辑
     * @param $reportType
     */
    protected function syncReportData($reportType) {
        switch ($reportType) {
            case 'operator':
                $redisPrefix = 'report';
                $kpiTablePrefix = 'KPIReport';
                break;
            case 'customer':
                $redisPrefix = 'reportcustomer';
                $kpiTablePrefix = 'KPIReportCustomer';
                break;
            case 'project':
                $redisPrefix = 'reportproject';
                $kpiTablePrefix = 'KPIReportProject';
                break;
            case 'case':
                $redisPrefix = 'reportcase';
                $kpiTablePrefix = 'KPIReportCase';
                break;
            default:
                throw new RuntimeException('报表类型[' . $reportType . ']未定义');
        }

        //用户数据前缀
        $pattern = $redisPrefix . ':' . $this->syncDate . ':*';
        $subStartPos = strlen($pattern) - 1;
        $keys = $this->scanKeys($pattern, 1000);

        $reportData = [];
        //整理获取节点数据
        foreach ($keys as $key) {
            $item = substr($key, $subStartPos);
            $item = explode(':', $item);
            //非关键标签，不统计
            if (!in_array($item[1], $this->kpiCode)) {
                continue;
            }
            $resumeCount = $this->redisClient->sCard($key);
            $fieldName = $this->kpiCodeInField[$item[1]];
            if (!isset($reportData[$item[0]][$fieldName])) {
                $reportData[$item[0]][$fieldName] = 0;
            }
            $reportData[$item[0]][$fieldName] += $resumeCount;
        }

        //计算统计周期 统计当日对应的 年  年月 当年第几周 年月日
        $syncDateToTime = strtotime($this->syncDate);
        $groupTimes = [
            'Year' => date('Y', $syncDateToTime),
            'Month' => date('Ym', $syncDateToTime),
            'Week' => date('W', $syncDateToTime),
            'Day' => date('Ymd', $syncDateToTime),
        ];
        //写入数据库中,每次取一百条
        while ($data = array_splice($reportData, 0, 100)) {
            //年月周日四张报表依次处理，如果sql执行出错，多次尝试操作，最终不行则记录详细日志，方便通过日志重新执行
            foreach ($groupTimes as $groupRange => $groupTime) {
                $table = $kpiTablePrefix . $groupRange;
                $retryTimes = 0;
                $result = false;
                while ($retryTimes < 3 && false === ($result = $this->updateToDb($table, $data, $groupTime))) {
                    $retryTimes++;
                }
                if (false === $result) {
                    Log::error('同步报表出错', [
                        'table' => $table,
                        'groupTime' => $groupTime,
                        'data' => $data,
                    ]);
                }
            }
        }

        //删除redis存储的记录键值
        while ($key = array_splice($keys, 0, 100)) {
//            $this->redisClient->del(...$key);
        }
    }

    /**
     * 数据同步写入数据库中
     * @param $table
     * @param $data
     * @param $groupTime
     * @return bool
     */
    protected function updateToDb($table, $data, $groupTime) {
        try {
            //step1:查询记录判断应该新增还是更新
            //step2：整合操作sql
            //step3：执行事务
            $searchIds = array_keys($data);
            $inFlag = substr(str_repeat('?,', count($searchIds)), 0, -1);
            $searchSql = 'SELECT searchid FROM ' . $table . ' WHERE searchid IN(' . $inFlag . ') AND grouptime=?';
            $searchVar = array_merge($searchIds, [$groupTime]);
            $searchList = Db::connect()->select($searchSql, $searchVar);
            $existSearchIds = [];
            foreach ($searchList as $item) {
                $existSearchIds[] = $item['searchid'];
            }
            //待执行事务脚本
            $executeStatements = [];
            $nowDateTime = date('Y-m-d H:i:s');
            $newSearchIds = array_diff($searchIds, $existSearchIds);
            foreach ($existSearchIds as $existSearchId) {
                $updSql = 'UPDATE ' . $table . ' SET ';
                $updVar = [];
                foreach ($this->kpiCodeInField as $codeField) {
                    $updSql .= $codeField . '=' . $codeField . '+?,';
                    $updVar[] = $data[$existSearchId][$codeField] ?? 0;
                }
                $updSql .= 'updatedate=?';
                $updVar[] = $nowDateTime;
                $executeStatements[] = Db::connect()->prepareStatement($updSql, $updVar);
            }

            if (!empty($newSearchIds)) {
                $codeFieldNameString = implode(',', $this->kpiCodeInField);
                $codeFieldVarString = substr(str_repeat('?,', count($this->kpiCodeInField) + 7), 0, -1);
                $insSql = 'INSERT INTO ' . $table . '(oprid,teamcode,groupid,grouptime,createdate,updatedate,searchid,' . $codeFieldNameString . ') VALUES ';
                $insVar = [[]];
                foreach ($newSearchIds as $newSearchId) {
//                    $newSearchId  oprid_teamcode_groupid 职位id，项目id，客户id或者0
                    $groupIds = explode('_', $newSearchId);

                    $insSql .= '(' . $codeFieldVarString . '),';
                    $tmpVar = [
                        $groupIds[0], $groupIds[1], $groupIds[2], $groupTime, $nowDateTime, $nowDateTime, $newSearchId
                    ];
                    foreach ($this->kpiCodeInField as $codeField) {
                        $tmpVar[] = $data[$newSearchId][$codeField] ?? 0;
                    }
                    $insVar[] = $tmpVar;
                }
                $insSql = substr($insSql, 0, -1);
                $insVar = array_merge(...$insVar);
                $executeStatements[] = Db::connect()->prepareStatement($insSql, $insVar);
            }
            if (count($executeStatements) == 1) {
                $executeStatements[0]->execute();
            } else {
                Db::connect()->prepareTransactionStatement(false, ...$executeStatements)->execute();
            }
            return true;
        } catch (\Exception $e) {
            Log::error('执行kpi同步' . $e->getMessage());
            return false;
        }
    }

    /**
     * 遍历获取所有键名
     * @param null $pattern
     * @param int $scanCount
     * @return array
     */
    protected function scanKeys($pattern = null, $scanCount = 0) {
        $keys = [[]];
        $iterator = null;

        while ($scanKeys = $this->redisClient->scan($iterator, $pattern, $scanCount)) {
            $keys[] = $scanKeys;
        }
        return array_merge(...$keys);
    }
}